BEGIN TRANSACTION
GO
ALTER TABLE dbo.data_SalesDetail ADD
	StoreID smallint NULL,
	GST numeric(13, 2) NOT NULL CONSTRAINT DF_data_SalesDetail_GST DEFAULT 0,
	GSTAmount numeric(13, 2) NOT NULL CONSTRAINT DF_data_SalesDetail_GSTAmount DEFAULT 0,
	Disc numeric(13, 4) NOT NULL CONSTRAINT DF_data_SalesDetail_Disc DEFAULT 0
GO
COMMIT

----------------------------

BEGIN TRANSACTION
GO
	-- UPDATE data_SalesDetail SET StoreID = 1

	-- UPDATE data_SalesDetail SET DiscP =  -1 * DiscP WHERE (ProductID IN (SELECT ProductID FROM gen_ProductsInfo WHERE (GroupID = 2)))

	-- UPDATE data_SalesDetail SET GST = 18 WHERE (ProductID IN (SELECT ProductID FROM gen_ProductsInfo WHERE (GroupID = 2)))

	-- UPDATE data_SalesDetail SET Disc = 18 WHERE (DiscP=0) AND (ProductID IN (SELECT ProductID FROM gen_ProductsInfo WHERE (GroupID = 2)))
	
	-- UPDATE data_SalesDetail SET Disc = GST - DiscP WHERE (ProductID IN (SELECT ProductID FROM gen_ProductsInfo WHERE (GroupID = 2)))

	-- UPDATE data_SalesDetail SET Disc = CASE WHEN DiscP = 0 THEN 18 ELSE CASE WHEN DiscP > 0 THEN 18 + DiscP ELSE 18 - DISCP END END WHERE (ProductID IN (SELECT ProductID FROM gen_ProductsInfo WHERE (GroupID = 2)))

	-- UPDATE data_SalesDetail SET DiscP =  GST - Disc WHERE (Disc <> GST ) AND (ProductID IN (SELECT ProductID FROM gen_ProductsInfo WHERE (GroupID = 2)))

	-- GPT FINAL QUERY
	
	UPDATE data_SalesDetail SET
		StoreID = 1,
		GST = 18,
		Disc = 18 + DiscP,
		DiscP = DiscP * -1
	WHERE ProductID IN (SELECT ProductID    FROM gen_ProductsInfo    WHERE GroupID = 2)

GO
COMMIT

----------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_ProductsInfo]
AS
SELECT     dbo.gen_ProductsInfo.GroupID, dbo.gen_GroupInfo.GroupTitle, dbo.gen_ProductsInfo.CategoryID, dbo.gen_CategoryInfo.CategoryTitle, dbo.gen_ProductsInfo.ProductID, 
                      dbo.gen_ProductsInfo.ProductCode, dbo.gen_ProductsInfo.ProductTitle, dbo.gen_ProductsInfo.UnitID, dbo.gen_Units.UnitTitle, dbo.gen_Units.Scale, dbo.gen_ProductsInfo.UnitPrice, 
                      dbo.gen_ProductsInfo.SalePrice, dbo.gen_ProductsInfo.Remarks, dbo.gen_ProductsInfo.Discontinue, dbo.gen_ProductsInfo.Opening, dbo.gen_ProductsInfo.DisplayInOrder, 
                      dbo.gen_ProductsInfo.UnitPrice2, dbo.gen_ProductsInfo.SalePrice2, dbo.gen_ProductsInfo.Weight, dbo.gen_ProductsInfo.InQty, dbo.gen_ProductsInfo.OutQty, dbo.gen_ProductsInfo.BalQty, 
                      dbo.gen_ProductsInfo.WH1, dbo.gen_ProductsInfo.WH2, dbo.gen_ProductsInfo.WH3
FROM         dbo.gen_ProductsInfo LEFT OUTER JOIN
                      dbo.gen_GroupInfo ON dbo.gen_ProductsInfo.GroupID = dbo.gen_GroupInfo.GroupID LEFT OUTER JOIN
                      dbo.gen_CategoryInfo ON dbo.gen_ProductsInfo.CategoryID = dbo.gen_CategoryInfo.CategoryID LEFT OUTER JOIN
                      dbo.gen_Units ON dbo.gen_ProductsInfo.UnitID = dbo.gen_Units.UnitId
WHERE     (0 = 0)

----------------------------

QUERY
=========

SELECT     ProductID, Retail, Disc, DiscP, GST, UnitRate, Quantity, Weight, RTAmount, DiscAmount, GSTAmount, Amount, RateList, StoreID, (Retail * Quantity) * (Disc / 100) AS Discount, (Retail * Quantity) 
                      * (GST / 100) AS GSTValue, (Retail - Retail * Disc / 100) + Retail * GST / 100 AS Rate, 
                      CASE WHEN Disc > GST THEN GST - Disc ELSE CASE WHEN Disc < GST THEN GST - DISC ELSE 0 END END AS DiscPErcent, Remarks
FROM         data_SalesDetail
WHERE     (ProductID IN
                          (SELECT     ProductID
                            FROM          gen_ProductsInfo
                            WHERE      (GroupID = 2)))
ORDER BY SalesID DESC